package com.teradata.feedProc;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.teradata.feedProc.beans.FeedInfo;
import com.teradata.feedProc.beans.FieldInfo;
import com.teradata.feedProc.enums.FieldDelimiterType;
import com.teradata.feedProc.enums.FileFormatType;

/**
 * Various utilities for connecting and interacting with database.
 */
public class DBUtils {

	// DB properties
	private final static String PROPERTY_DB_USERNAME = "db.username";
	private final static String PROPERTY_DB_PASSWORD = "db.password";
	private final static String PROPERTY_DB_URL = "db.url";
	private final static String PROPERTY_DB_DRIVER = "db.driver";

	// Feed table columns
	private final static String FILE_FORMAT_TYPE_COLUMN = "file_frmt_typ";
	private final static String DELIMITER_TEXT_COLUMN = "clmn_dlm_txt";

	// Field table columns
	private final static String FIELD_INDEX_COLUMN = "DATA_OBJ_CLMN_SEQ_NBR";
	private final static String FIELD_NAME_COLUMN = "DATA_OBJ_CLMN_NM";
	private final static String FIELD_LENGTH_COLUMN = "CLMN_LGTH_NBR";
	private final static String FIELD_DATE_BOOLEAN_COLUMN = "DATEFLG";
	private final static String FIELD_DATA_FORMAT_COLUMN = "DATA_OBJ_CLMN_FORMAT_TXT";
	private final static String FIELD_DEFAULT_VALUE_COLUMN = "CLMN_DFLT_VAL_TXT";

	/**
	 * Get connection to database.
	 * 
	 * @return connection {@inheritDoc Connection}
	 */
	public static Connection createConnection() {
		return createConnection(Config.getProperty(PROPERTY_DB_URL), Config.getProperty(PROPERTY_DB_USERNAME),
				Config.getProperty(PROPERTY_DB_PASSWORD));
	}

	/**
	 * Get connection to database.
	 * 
	 * @return connection {@inheritDoc Connection}
	 */
	public static Connection createConnection(String strUrl, String strUserName, String strPassword) {
		try {
			Class.forName(Config.getProperty(PROPERTY_DB_DRIVER));
			Connection conn = DriverManager.getConnection(strUrl, strUserName, strPassword);
			if (conn != null) {
				return conn;
			} else {
				System.out.println("Failed to make a connection.");
				return null;
			}
		} catch (ClassNotFoundException cnfe) {
			System.out.println("ClassNotFoundException encountered. " + cnfe.getMessage());
			return null;
		} catch (Exception sqle) {
			System.out.println("Could not connect to Database");
			System.out.println("Connection failed! " + sqle.getMessage());
			return null;
		}
	}

	/**
	 * Close database connection.
	 * 
	 * @param conn
	 *            - connection to close
	 */
	public static void closeConnection(final Connection conn) {
		try {
			conn.close();
		} catch (SQLException sqle) {
			System.out.println("Failed to close connection. " + sqle.getMessage());
		}
	}

	/**
	 * Run SQL query to get {@link FeedInfo}.
	 * 
	 * @param conn
	 *            - db connection
	 * @param feedName
	 *            - name of feed
	 * @param sourceName
	 *            - name of source file
	 * @param targetName
	 *            - name of target file
	 */
	public static FeedInfo getFeedInfo(final Connection conn, final String feedName, final String dbName, final String sourceName,
			final String targetName) {
		final String query = Config.getProperty("sql.feed.query", feedName, dbName);
		final FeedInfo feedInfo = new FeedInfo();
		Boolean hasInfo = false;
		feedInfo.setStrSrcLocName(Config.getProperty("source.location"));
		feedInfo.setStrSrcObjName(sourceName);
		feedInfo.setStrDestLocName(Config.getProperty("target.location"));
		feedInfo.setStrDestObjName(targetName);
		feedInfo.setStrFeedName(feedName);
		feedInfo.setStrDbName(dbName);
			try {
				PreparedStatement statement = conn.prepareStatement(query);
				statement.setMaxRows(1);
				ResultSet resultSet = statement.executeQuery();

				if (resultSet.next()) {
					hasInfo = true;
					feedInfo.setFormatType(FileFormatType.valueOf(resultSet.getString(FILE_FORMAT_TYPE_COLUMN)));
					if (feedInfo.getFormatType().equals(FileFormatType.valueOf("D"))) {
						final String delim = resultSet.getString(DELIMITER_TEXT_COLUMN);
						feedInfo.setDelimiterType(null);

						for (FieldDelimiterType fieldDelimiterType : FieldDelimiterType.values()) {
							if (fieldDelimiterType.getValue().contains(delim)) {
								feedInfo.setDelimiterType(fieldDelimiterType);
								break;
							}
						}
						if (feedInfo.getDelimiterType() == null) {
							System.out.println("Delimiter Type not found.");
							System.exit(1);
						}
					}
				}
				if (!hasInfo) {
					System.out.println("FEED information not found in MDF");
					System.exit(1);
				}
				resultSet.close();
			} catch (SQLException sqle) {
				System.out.println("SQLException encountered when collecting feed info. " + sqle.getMessage());
				return null;
			}
		return feedInfo;
	}

	/**
	 * Run SQL query to get date formats.
	 * 
	 * @param conn
	 *            - connection to db
	 * @param feedInfo
	 *            - {@link FeedInfo}
	 * @return list - list of {@link FieldInfo}
	 */
	public static List<FieldInfo> getFieldInfo(final Connection conn, final FeedInfo feedInfo) {
		ArrayList<FieldInfo> fieldInfoList = new ArrayList<FieldInfo>();
		final String query = Config.getProperty("sql.dates.query", feedInfo.getStrFeedName(), feedInfo.getStrDbName());
			try {
				PreparedStatement statement = conn.prepareStatement(query);
				ResultSet resultSet = statement.executeQuery();
				while (resultSet.next()) {
					FieldInfo fieldInfo = new FieldInfo();
					fieldInfo.setiFieldIndex(resultSet.getInt(FIELD_INDEX_COLUMN));
					fieldInfo.setStrFieldName(resultSet.getString(FIELD_NAME_COLUMN).trim());
					fieldInfo.setiFieldLength(resultSet.getInt(FIELD_LENGTH_COLUMN));
					if (resultSet.getString(FIELD_DATE_BOOLEAN_COLUMN).equals("Y")) {
						fieldInfo.setbIsDateType(true);
					} else {
						fieldInfo.setbIsDateType(false);
					}
					if (fieldInfo.isbIsDateType()) {
						if (!fieldInfo.checkFormats(resultSet.getString(FIELD_DATA_FORMAT_COLUMN).toUpperCase())) {
							System.out.println("Format: " + resultSet.getString(FIELD_DATA_FORMAT_COLUMN)
									+ " is not supported. Please add to the list of supported formats in DateFormats.java");
							System.exit(1);
						}
					}
					fieldInfo.setStrFormat(resultSet.getString(FIELD_DATA_FORMAT_COLUMN));
					fieldInfo.setStrDefaultVal(resultSet.getString(FIELD_DEFAULT_VALUE_COLUMN));
					fieldInfoList.add(fieldInfo);
				}
				resultSet.close();
			} catch (SQLException sqle) {
				System.out.println("SQLException encountered when collecting field info. " + sqle.getMessage());
				return null;
			}
			
		return fieldInfoList;
	}

	public static String getCmLookup(String dbName, String clear, String dbURL, String userID, String password) {
		
		Connection conn = DBUtils.createConnection(dbURL, userID, password);
		
		String token_value = null;
		
		try {
			conn.close();
		} catch (SQLException e) {
			System.out.println("Trouble closing connection with database");
		}
		return token_value;
	}
}